﻿using System;
using System.Collections.Generic;
using System.Data;
using SdmxMl.Common;
using SdmxMl.Manager;
using SdmxMl.Structure;
using SdmxMl.GenericMetadata;
using System.IO;
using SdmxMl.SmsImportCfg;

namespace PluginMeb
{
    public class ImportMetasetFromExcel
    {
    //    public const string MebAgency = "CH1_MEB";
        public const string KEY_SCHULART = "SCHKT";
        public const string KEY_BILDUNG_KT = "BJKT";
        public const string KEY_KLASSE_CH = "KLCH";
        public const string KEY_BILDUNG_CH = "BJCH";
        public const string KEY_ALTER_MIN = "AMIN";
        public const string KEY_ALTER_MAX = "AMAX";
        public const string KEY_GULT_AB = "VF";
        public const string KEY_GULT_BIS = "VT";
        public const string REPORT_COMMENT = "COMMENT";

        public const string EMPTY_MARKER = ".";

        private MetadataSetType metadataSet;
        private MetadataStructureDefinitionType msd;
        private SmManager sm;
        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;


        public static MetadataSetType CreateMetaset(int cantonIndex, string agencyId)
        {
            MetadataSetType ms = new MetadataSetType();
            ms.AgencyId = agencyId;
            ms.Id = "MS_MEB_MX_" + cantonIndex.ToString().PadLeft(2, '0');
            ms.MetadataStructureRef.AgencyId = agencyId;
            ms.MetadataStructureRef.Id = "MSD_MATRIX_CH";
            ms.Name.UpdateDefaultText("Matrix for canton code: " + cantonIndex.ToString());
            ms.AttributeValueSetList.Clear();
            ms.ReportRef = "R_MATRIX";
            return ms;

        }
        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }

        public void RedefineParent()
        {
            sm = new SmManager();
            CodeListType clt = sm.GetArtefact(SdmxArtefactType.CodeLists, new ArtefactRefBase("CH1_MEB+CL_KLSCHULART+1.0"), true) as CodeListType;
            foreach (CodeType c in clt.CodeList)
            {
                int index = c.Id.IndexOf('_');
                if (index > 0)
                {
                    string cantonId = c.Id.Substring(index + 1);
                    c.Parent = cantonId;
                    foreach (Annotation a in c.AnnotationList)
                    {
                        string title = a.Text.ToString();
                        a.Title = title;
                        a.Text = new MultiLang(string.Empty);
                    }
                }
            }
            clt.CodeList.HasHierarchy = true;
            object ob = NavigationManager.LoadFromSmManager(sm);

        }

        public void ImportFromExcel(CodeListType cltCanton, string agencyId)
        {
            spreadsheetConcept = LoadSources(@"C:\Doc\MEB\meta_CH_ergaenzung__2013-12-19_15-41-00-ACH.xls");

             sm = new SmManager();

             Dictionary<int, MetadataSetType> dicoMatrix = new Dictionary<int, MetadataSetType>(28);

            // Fetch MSD and report for SAKE quest
             metadataSet = CreateMetaset(0, agencyId);
             msd = sm.GetArtefact(SdmxArtefactType.MetadataStructureDefinitions,
                metadataSet.MetadataStructureRef, true) as MetadataStructureDefinitionType;
            ReportStructureType report = msd.ReportStructureList[0];

            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            for (int row = 2; row <= dt.Rows.Count; row++)
            {
                string schkt = wh.GetCellContent(row, 1);
                if (schkt.Length == 0)
                    continue;

                int kt = int.Parse(schkt.Substring(schkt.Length - 2));
                string schulart = schkt.Substring(0, schkt.Length - 3);

                if (dicoMatrix.ContainsKey(kt) == false)
                {
                    metadataSet = CreateMetaset(kt, agencyId);
                    dicoMatrix.Add(kt, metadataSet);
                }
                else
                {
                    metadataSet = dicoMatrix[kt];
                }

                AttributeValueSetType atv = new AttributeValueSetType(msd, report, metadataSet);
                // The target values
                atv.TargetValuesTypeList.SetComponentValue(KEY_BILDUNG_CH, GetKeyValue(wh.GetCellContent(row, 6)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_ALTER_MIN, GetKeyValue(wh.GetCellContent(row, 7)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_ALTER_MAX, GetKeyValue(wh.GetCellContent(row, 8)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_GULT_AB, GetKeyValue(wh.GetCellContent(row, 9)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_GULT_BIS, GetKeyValue(wh.GetCellContent(row, 10)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_KLASSE_CH, GetKeyValue(wh.GetCellContent(row, 5)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_BILDUNG_KT, GetKeyValue(wh.GetCellContent(row, 4)));
                atv.TargetValuesTypeList.SetComponentValue(KEY_SCHULART, schulart);

                // The reported attributes (no comment at creation)
                ReportedAttributeType ra = atv.ReportedAttributeList.GetReportedAttributeOfConcept(REPORT_COMMENT);
                if (ra != null)
                {
                    ra.Value = new MultiLang(EMPTY_MARKER);
                }


                metadataSet.AttributeValueSetList.Add(atv);
            }

            foreach (MetadataSetType mds in dicoMatrix.Values)
            {
                SmManager smms = new SmManager();

                smms.PutArtefact(SdmxArtefactType.MetadataSet, mds);
                object ob = NavigationManager.LoadFromSmManager(smms);
               // NavigationManager.RequestNavigation(ob);
            }

       }

        private string GetKeyValue(string val)
        {
            if (string.IsNullOrEmpty(val))
                return EMPTY_MARKER;
            return val;
        }

    }
}
